﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;

using TntOrm.Structures;
using TntOrm.Utility;

using System.Data;

namespace TntOrm
{
    public abstract class OrmDataBaseObject
    {
        #region Fetch
        public void FetchByID( int id )
        {
            TableMapping Mapper = TntOrm.OrmManager.GetTable( this.ToString( ) );

            string SelectSql = "SELECT ";
            string WhereSql = " WHERE ";

            foreach( FieldMapping TableField in Mapper.Fields )
            {
                SelectSql += Mapper.TableName + "." + TableField.FieldName + ", ";

                if( TableField.PrimaryKey )
                {
                    WhereSql += TableField.FieldName + " = \'" + id.ToString( ) + "\'";
                }
            }

            SelectSql = SelectSql.Substring( 0, SelectSql.Length - 2 );
            SelectSql += " FROM " + Mapper.TableName + WhereSql;

            SqlHelper.ReturnData Data
               = SqlHelper.Execute( new System.Data.SqlClient.SqlCommand( SelectSql ), SqlHelper.ExecuteType.Fill );

            if( Data.ReturnSet == null || Data.ReturnSet.Tables.Count == 0 || Data.ReturnSet.Tables[ 0 ].Rows.Count == 0 )
            {
                return;
            }

            this.SetData( Data.ReturnSet.Tables[ 0 ].Rows[ 0 ] );
        }

        public T[] FetchAll<T>( ) where T : new( )
        {
            TableMapping Mapper = TntOrm.OrmManager.GetTable( this.ToString( ) );

            string SelectSql = "SELECT ";

            foreach( FieldMapping TableField in Mapper.Fields )
            {
                SelectSql += TableField.FieldName + ", ";
            }

            SelectSql = SelectSql.Substring( 0, SelectSql.Length - 2 );
            SelectSql += " FROM " + Mapper.TableName;

            SqlHelper.ReturnData Data
               = SqlHelper.Execute( new System.Data.SqlClient.SqlCommand( SelectSql ), SqlHelper.ExecuteType.Fill );

            if( Data.ReturnSet == null || Data.ReturnSet.Tables.Count == 0 || Data.ReturnSet.Tables[ 0 ].Rows.Count == 0 )
            {
                return null;
            }

            T[] ReturnData = new T[ Data.ReturnSet.Tables[ 0 ].Rows.Count ];

            for( int i = 0; i < Data.ReturnSet.Tables[ 0 ].Rows.Count; ++i )
            {
                ReturnData[ i ] = ( T ) Activator.CreateInstance( this.GetType( ) );

                ReturnData[ i ].GetType( ).InvokeMember( "SetData", BindingFlags.InvokeMethod |
                                                                BindingFlags.Default, null, ReturnData[ i ],
                                                                new object[] { Data.ReturnSet.Tables[ 0 ].Rows[ i ] } );
            }

            return ReturnData;
        }

        public T[] FetchLimit<T>( int StartIndex, int Count, bool LookUp ) where T : new( )
        {
            TableMapping Mapper = TntOrm.OrmManager.GetTable( this.ToString( ) );

            string SelectSql = "SELECT TOP " + Count + " ";

            foreach( FieldMapping TableField in Mapper.Fields )
            {
                SelectSql += TableField.FieldName + ", ";
            }

            SelectSql = SelectSql.Substring( 0, SelectSql.Length - 2 );
            SelectSql += " FROM " + Mapper.TableName;

            string PrimaryKeyName = GetPrimaryKeyName( );
            string OrderBy = " ORDER BY " + PrimaryKeyName + " ";

            SelectSql += string.Format( " WHERE {0} NOT IN ( SELECT TOP {1} {0} FROM {2} {3} ) ",
                                  PrimaryKeyName, StartIndex, Mapper.TableName, OrderBy );

            if( LookUp )
            {
                string WhereSql = LookUpString( );

                if( WhereSql != string.Empty || !WhereSql.Equals( "" ) )
                {
                    SelectSql += " AND " + WhereSql;
                }
            }

            SelectSql += " " + OrderBy;

            SqlHelper.ReturnData Data
               = SqlHelper.Execute( new System.Data.SqlClient.SqlCommand( SelectSql ), SqlHelper.ExecuteType.Fill );

            if( Data.ReturnSet == null || Data.ReturnSet.Tables.Count == 0 || Data.ReturnSet.Tables[ 0 ].Rows.Count == 0 )
            {
                return null;
            }

            T[] ReturnData = new T[ Data.ReturnSet.Tables[ 0 ].Rows.Count ];

            for( int i = 0; i < Data.ReturnSet.Tables[ 0 ].Rows.Count; ++i )
            {
                ReturnData[ i ] = ( T ) Activator.CreateInstance( this.GetType( ) );

                ReturnData[ i ].GetType( ).InvokeMember( "SetData", BindingFlags.InvokeMethod |
                                                                BindingFlags.Default, null, ReturnData[ i ],
                                                                new object[] { Data.ReturnSet.Tables[ 0 ].Rows[ i ] } );
            }

            return ReturnData;
        }

        private string LookUpString( )
        {
            TableMapping Mapper = TntOrm.OrmManager.GetTable( this.ToString( ) );

            string WhereSql = "";

            foreach( FieldMapping TableField in Mapper.Fields )
            {
                if( TableField.LookUp && GetPropertyValue( TableField.PropertyName ) != null )
                {
                    object Value = null;

                    if( TableField.Reference == null )
                    {
                        Value = GetPropertyValue( TableField.PropertyName );
                    }
                    else
                    {
                        object ReferenceData = GetPropertyValue( TableField.PropertyName );

                        Value =
                            ReferenceData.GetType( ).GetProperty( TableField.Reference.CrossPropertyName )
                                .GetValue( ReferenceData, null );
                    }

                    if( Value != null && !Value.Equals( string.Empty ) )
                    {
                        WhereSql += TableField.FieldName + " = '" + Value.ToString( ) + "' AND ";
                    }
                }
            }

            if( WhereSql != string.Empty )
            {
                WhereSql = WhereSql.Substring( 0, WhereSql.Length - 4 );
            }

            return WhereSql;
        }

        public T[] FetchLookUp<T>( ) where T : new( )
        {
            TableMapping Mapper = TntOrm.OrmManager.GetTable( this.ToString( ) );

            string SelectSql = "SELECT ";

            foreach( FieldMapping TableField in Mapper.Fields )
            {
                SelectSql += TableField.FieldName + ", ";
            }

            SelectSql = SelectSql.Substring( 0, SelectSql.Length - 2 );
            SelectSql += " FROM " + Mapper.TableName;

            string WhereSql = LookUpString( );

            if( WhereSql != string.Empty || !WhereSql.Equals( "" ) )
            {
                SelectSql += " WHERE " + WhereSql;
            }

            SqlHelper.ReturnData Data
               = SqlHelper.Execute( new System.Data.SqlClient.SqlCommand( SelectSql ), SqlHelper.ExecuteType.Fill );

            if( Data.ReturnSet == null || Data.ReturnSet.Tables.Count == 0 || Data.ReturnSet.Tables[ 0 ].Rows.Count == 0 )
            {
                return null;
            }

            T[] ReturnData = new T[ Data.ReturnSet.Tables[ 0 ].Rows.Count ];

            for( int i = 0; i < Data.ReturnSet.Tables[ 0 ].Rows.Count; ++i )
            {
                ReturnData[ i ] = ( T ) Activator.CreateInstance( this.GetType( ) );

                ReturnData[ i ].GetType( ).InvokeMember( "SetData", BindingFlags.InvokeMethod |
                                                                BindingFlags.Default, null, ReturnData[ i ],
                                                                new object[] { Data.ReturnSet.Tables[ 0 ].Rows[ i ] } );
            }

            return ReturnData;
        }
        #endregion

        #region Load cross data
        public P[] GetCrossData< P >( string CrossRefName ) where P : new( )
        {
            TableMapping Mapper = TntOrm.OrmManager.GetTable( this.ToString( ) );

            if( !Mapper.CrossReferences.ContainsKey( CrossRefName ) )
            {
                return null;
            }

            object ThisPrimaryKey = this.GetPrimaryKeyValue( );

            if( ThisPrimaryKey == null )
            {
                return null;
            }

            TntOrm.Attributes.OrmCrossReference Reference = Mapper.CrossReferences[ CrossRefName ];

            TableMapping ReferenceToType = TntOrm.OrmManager.GetTable( Reference.ObjectType );
            TableMapping CrossTableMapper = TntOrm.OrmManager.GetTable( Reference.CrossTable );
            
            string SelectSql = "SELECT * FROM "
                        + ReferenceToType.TableName + " WHERE " + Reference.ObjectPrimaryKey  
                        + " IN ( SELECT DISTINCT "
                            + Reference.ObjectPrimaryKey + " FROM " + CrossTableMapper.TableName + " WHERE "
                            + Reference.LookUpField + " = "
                            + ThisPrimaryKey.ToString( )  
                        +")";

            SqlHelper.ReturnData Data
               = SqlHelper.Execute( new System.Data.SqlClient.SqlCommand( SelectSql ), SqlHelper.ExecuteType.Fill );
            
            if( Data.ReturnSet == null || Data.ReturnSet.Tables.Count == 0 || Data.ReturnSet.Tables[ 0 ].Rows.Count == 0 )
            {
                return null;
            }

            P[] ReturnData = new P[ Data.ReturnSet.Tables[ 0 ].Rows.Count ];

            for( int i = 0; i < Data.ReturnSet.Tables[ 0 ].Rows.Count; ++i )
            {
                ReturnData[ i ] = new P( ); //( P ) Activator.CreateInstance( Reference.ObjectType.GetType( ) );
                ReturnData[ i ].GetType( ).InvokeMember( "SetData", BindingFlags.InvokeMethod |
                                                                BindingFlags.Default, null, ReturnData[ i ],
                                                                new object[] { Data.ReturnSet.Tables[ 0 ].Rows[ i ] } );
            }

            return ReturnData;
        }
        #endregion

        #region Set data
        public void SetData( DataRow ItemData )
        {
            TableMapping Mapper = TntOrm.OrmManager.GetTable( this.ToString( ) );

            foreach( FieldMapping TableField in Mapper.Fields )
            {
                if( !Convert.IsDBNull( ItemData[ TableField.FieldName ] ) )
                {
                    if( TableField.Reference == null )
                    {
                        this.SetProperty( TableField.PropertyName, ItemData[ TableField.FieldName ] );
                    }
                    else if( TableField.Reference.Belonge == TntOrm.Attributes.BelongType.HasOne )
                    {
                        object ReferenceData = Activator.CreateInstance( TableField.Reference.ReferenceType );

                        ReferenceData.GetType( ).InvokeMember( "FetchByID", BindingFlags.InvokeMethod |
                                                                    BindingFlags.Default,
                                                                    null, ReferenceData,
                                                                    new object[] { Convert.ToInt32( ItemData[ TableField.FieldName ] ) } );

                        this.SetProperty( TableField.PropertyName, ReferenceData );
                    }
                }
            }
        }
        #endregion

        #region CreateTable
        public void CreateTable( )
        {
            TableMapping Mapper = TntOrm.OrmManager.GetTable( this.ToString( ) );

            string CreateTableSQL = "CREATE TABLE " + Mapper.TableName + "(";
            string PrimaryKey = string.Empty;

            foreach( FieldMapping TableField in Mapper.Fields )
            {
                CreateTableSQL += "[" + TableField.FieldName
                               + "] [" + TableField.FieldAttribute.Type.ToString( ) + "]";

                if( TableField.FieldAttribute.Length != null && TableField.FieldAttribute.Length != -1 )
                {
                    CreateTableSQL += " (" + TableField.FieldAttribute.Length + ")";
                }

                if( TableField.PrimaryKey )
                {
                    CreateTableSQL += " IDENTITY(1,1) ";
                    PrimaryKey = " PRIMARY KEY(" + TableField.FieldName + ")";
                }

                if( TableField.FieldAttribute.IsNullAble )
                {
                    CreateTableSQL += " NULL";
                }
                else
                {
                    CreateTableSQL += " NOT NULL";
                }

                if( TableField.FieldAttribute.DefaultValue != null )
                {
                    CreateTableSQL += " DEFAULT (" + TableField.FieldAttribute.DefaultValue + ")";
                }

                CreateTableSQL += ", ";
            }

            if( PrimaryKey.Equals( string.Empty ) )
            {
                CreateTableSQL = CreateTableSQL.Substring( 0, CreateTableSQL.Length - 2 );
            }
            else
            {
                CreateTableSQL += PrimaryKey;
            }

            CreateTableSQL += " );";

            SqlHelper.Execute( new System.Data.SqlClient.SqlCommand( CreateTableSQL ), SqlHelper.ExecuteType.Execute );
        }
        #endregion

        #region Save (insert or update) data
        public int Save( )
        {
            TableMapping Mapper = TntOrm.OrmManager.GetTable( this.ToString( ) );

            foreach( FieldMapping TableField in Mapper.Fields )
            {
                if( TableField.PrimaryKey )
                {
                    if( GetPropertyValue( TableField.PropertyName ) == null )
                    {
                        return Insert( );
                    }
                    else
                    {
                        return Update( );
                    }
                }
            }

            return -1;
        }

        public int Insert( )
        {
            TableMapping Mapper = TntOrm.OrmManager.GetTable( this.ToString( ) );

            string InsertSql = "INSERT INTO " + Mapper.TableName + " (";
            string InsertSql2 = "";

            foreach( FieldMapping TableField in Mapper.Fields )
            {
                if( !TableField.PrimaryKey )
                {
                    InsertSql += TableField.FieldName + ", ";

                    if( !TableField.PrimaryKey )
                    {
                        if( TableField.Reference == null )
                        {
                            InsertSql2 += "'" + GetPropertyValue( TableField.PropertyName ) + "', ";
                        }
                        else
                        {
                            InsertSql2 += "'" +
                                GetPropertyValue( TableField.PropertyName ).GetType( ).
                                    GetProperty( TableField.Reference.CrossPropertyName ).
                                    GetValue( GetPropertyValue( TableField.PropertyName ), null ) + "', ";
                        }
                    }
                }
            }
            
            InsertSql = InsertSql.Substring( 0, InsertSql.Length - 2 );
            InsertSql += " ) VALUES (" + InsertSql2;

            InsertSql = InsertSql.Substring( 0, InsertSql.Length - 2 );
            InsertSql += ")";

            SqlHelper.ReturnData Data 
                =  SqlHelper.Execute( new System.Data.SqlClient.SqlCommand( InsertSql ), SqlHelper.ExecuteType.Execute );

            return Data.ReturnValue;
        }

        public int Update( )
        {
            TableMapping Mapper = TntOrm.OrmManager.GetTable( this.ToString( ) );

            string UpdateSql = "UPDATE " + Mapper.TableName + " SET ";
            string WhereClause = string.Empty;

            foreach( FieldMapping TableField in Mapper.Fields )
            {
                if( !TableField.PrimaryKey )
                {
                    object Value = null;

                    if( TableField.Reference == null )
                    {
                        Value = GetPropertyValue( TableField.PropertyName ).ToString( ); 
                    }
                    else
                    {
                        Value = GetPropertyValue( TableField.PropertyName ).GetType( ).
                                        GetProperty( TableField.Reference.CrossPropertyName ).
                                        GetValue( GetPropertyValue( TableField.PropertyName ), null );
                    }

                    if( Value != null )
                    {
                        UpdateSql += TableField.FieldName + " = '" + Value.ToString( ) + "', ";
                    }                    
                }
                else
                {
                    if( GetPropertyValue( TableField.PropertyName ) == null )
                    {
                        throw new Exception( "Ненайдено значение первичного ключа." );
                    }

                    WhereClause += TableField.FieldName + " = '" + GetPropertyValue( TableField.PropertyName ) + "'";
                }
            }

            if( WhereClause == string.Empty )
            {
                throw new Exception( "Ненайден первичный ключ." );
            }

            UpdateSql = UpdateSql.Substring( 0, UpdateSql.Length - 2 );
            UpdateSql += " WHERE " + WhereClause;

            SqlHelper.ReturnData Data
                = SqlHelper.Execute( new System.Data.SqlClient.SqlCommand( UpdateSql ), SqlHelper.ExecuteType.Execute );

            return Data.ReturnValue;
        }
        #endregion

        #region Get and set property, set primary key
        public void SetPrimaryKey( object Value )
        {
            TableMapping Mapper = TntOrm.OrmManager.GetTable( this.ToString( ) );

            foreach( FieldMapping TableField in Mapper.Fields )
            {
                if( TableField.PrimaryKey )
                {
                    SetProperty( TableField.PropertyName, Value );
                    return;
                }
            }
        }

        public object GetPrimaryKeyValue( )
        {
            TableMapping Mapper = TntOrm.OrmManager.GetTable( this.ToString( ) );

            foreach( FieldMapping TableField in Mapper.Fields )
            {
                if( TableField.PrimaryKey )
                {
                    return GetPropertyValue( TableField.PropertyName );
                }
            }

            throw new Exception( "Ненайдено значение первичного ключа." );
        }

        public string GetPrimaryKeyName( )
        {
            TableMapping Mapper = TntOrm.OrmManager.GetTable( this.ToString( ) );

            foreach( FieldMapping TableField in Mapper.Fields )
            {
                if( TableField.PrimaryKey )
                {
                    return TableField.FieldName;
                }
            }

            throw new Exception( "Ненайдено значение первичного ключа." );
        }

        protected void SetProperty( string Name, object Value )
        {
            PropertyInfo Property
                        = this.GetType( ).GetProperty( Name
                            , BindingFlags.Instance | BindingFlags.Public | BindingFlags.GetProperty );

            Property.SetValue( this, Value, null );  
        }

        protected object GetPropertyValue( string Name )
        {
            PropertyInfo Property
                        = this.GetType( ).GetProperty( Name
                            , BindingFlags.Instance | BindingFlags.Public | BindingFlags.GetProperty );

            return Property.GetValue( this, null );
        }
        #endregion
    }
}